IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateAnimalProtocolBoardReviews]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UpdateAnimalProtocolBoardReviews]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



/***************************************************************************
* Name:			UpdateAnimalProtocolBoardReviews
* Purpose:		Update ProtocolSubmission with any updates from Rex, f
***************************************************************************/
CREATE PROCEDURE dbo.UpdateAnimalProtocolBoardReviews
AS
BEGIN

	SET NOCOUNT ON


		Update [ProtocolBoardReview] Set DateLastModified = Stg.DateLastModified
			FROM [Stg_AnimalProtocolBoardReview] Stg
			Where IsAnimalBR = 1
				AND [ProtocolBoardReview].Id = Stg.ProtocolBoardReviewId 
				AND ISNULL([ProtocolBoardReview].DateLastModified, '') != ISNULL(Stg.DateLastModified, '')
				AND Stg.DateLastModified IS NOT NULL
				AND Stg.ToBeUpdated = 1

		Update [ProtocolBoardReview] Set MeetingDate = Stg.MeetingDt
			FROM [Stg_AnimalProtocolBoardReview] Stg
			Where IsAnimalBR = 1
				AND [ProtocolBoardReview].Id = Stg.ProtocolBoardReviewId AND
				ISNULL([ProtocolBoardReview].MeetingDate, '') != ISNULL(Stg.MeetingDt, '')
			--	AND Stg.MeetingDt IS NOT NULL
				AND Stg.ToBeUpdated = 1
		
		Update [ProtocolBoardReview] Set NotificationDate = Stg.dtNotified
			FROM [Stg_AnimalProtocolBoardReview] Stg
			Where IsAnimalBR = 1
				AND [ProtocolBoardReview].Id = Stg.ProtocolBoardReviewId AND
				ISNULL([ProtocolBoardReview].NotificationDate, '') != ISNULL(Stg.[dtNotified], '')
			--	AND Stg.[dtNotified] IS NOT NULL
				AND Stg.ToBeUpdated = 1
				
		Update [ProtocolBoardReview] Set StatusModifiedDate = Stg.dtStatus
			FROM [Stg_AnimalProtocolBoardReview] Stg
			Where IsAnimalBR = 1
				AND [ProtocolBoardReview].Id = Stg.ProtocolBoardReviewId AND
				ISNULL([ProtocolBoardReview].StatusModifiedDate, '') != ISNULL(Stg.dtStatus, '')
				AND Stg.dtStatus IS NOT NULL
				AND Stg.ToBeUpdated = 1

		Update [ProtocolBoardReview] Set BoardId = Stg.BoardId
			FROM [Stg_AnimalProtocolBoardReview] Stg
			Where IsAnimalBR = 1
				AND [ProtocolBoardReview].Id = Stg.ProtocolBoardReviewId AND
				ISNULL([ProtocolBoardReview].BoardId, 0) != ISNULL(Stg.BoardId, 0)
				AND Stg.BoardId IS NOT NULL
				AND Stg.ToBeUpdated = 1
				
		Update [ProtocolBoardReview] Set ReviewProcessId = Stg.BoardReviewTypeId
			FROM [Stg_AnimalProtocolBoardReview] Stg
			Where IsAnimalBR = 1
				AND [ProtocolBoardReview].Id = Stg.ProtocolBoardReviewId AND
				ISNULL([ProtocolBoardReview].ReviewProcessId, 0) != ISNULL(Stg.BoardReviewTypeId, 0)
			--	AND Stg.BoardReviewTypeId IS NOT NULL
				AND Stg.ToBeUpdated = 1				
				
		Update [ProtocolBoardReview] Set BoardStatusId = Stg.BoardStatusId
			FROM [Stg_AnimalProtocolBoardReview] Stg
			Where IsAnimalBR = 1
				AND [ProtocolBoardReview].Id = Stg.ProtocolBoardReviewId AND
				ISNULL([ProtocolBoardReview].BoardStatusId, 0) != ISNULL(Stg.BoardStatusId, 0)
				AND Stg.BoardStatusId IS NOT NULL
				AND Stg.ToBeUpdated = 1	

		Update [ProtocolBoardReview] Set IsCurrent = Stg.bCurrentStatus
			FROM [Stg_AnimalProtocolBoardReview] Stg
			Where IsAnimalBR = 1
				AND [ProtocolBoardReview].Id = Stg.ProtocolBoardReviewId AND
				[ProtocolBoardReview].IsCurrent != bCurrentStatus

				
		-- since it's a text field, we can't compare them, but if it's updated, DateLastModified is also updated
		UPDATE [ProtocolBoardReview] SET [RTFNotificationText] = Stg.sRTFNote
			FROM [Stg_AnimalProtocolBoardReview] Stg
			Where IsAnimalBR = 1
				AND [ProtocolBoardReview].Id = Stg.ProtocolBoardReviewId 
				AND Stg.ToBeUpdated = 1	

		-- since it's a text field, we can't compare them,  but if it's updated, DateLastModified is also updated
		UPDATE [ProtocolBoardReview] SET [NotificationText] = Stg.sNote
			FROM [Stg_AnimalProtocolBoardReview] Stg
			Where IsAnimalBR = 1
				AND [ProtocolBoardReview].Id = Stg.ProtocolBoardReviewId 
				AND Stg.ToBeUpdated = 1	

		-- Update RexBoardDetailId - it will be different if the protocol is cloned; 
		-- It's already marked as updated because DateLastModified is also updated								
		UPDATE [ProtocolBoardReview] SET [RexBoardDetailId] = Stg.RexNBoardReviewId
			FROM [Stg_AnimalProtocolBoardReview] Stg
			Where IsAnimalBR = 1
				AND [ProtocolBoardReview].Id = Stg.ProtocolBoardReviewId 
				AND ISNULL([ProtocolBoardReview].RexBoardDetailId, 0) != RexNBoardReviewId
				AND Stg.ToBeUpdated = 1	
	END
		
	SET NOCOUNT OFF



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
